﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class Products : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd;

    void InitializeAOO()
    {
        con = new SqlConnection();
        con.ConnectionString = WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        cmd = new SqlCommand();
        cmd.Connection = con;
    }

    void loadCombobox()
    {
        InitializeAOO();
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetCategoriesID";
        SqlDataReader dr = cmd.ExecuteReader();
        while(dr.Read())
        {
            ddlCategoryID.Items.Add(dr[0].ToString());
        }
        dr.Close();
        con.Close();
    }

    void loadSupplier()
    {
        InitializeAOO();
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSupplierID";
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            ddlSupplierID.Items.Add(dr[0].ToString());
        }
        dr.Close();
        con.Close();
    }

    void Fill()
    {
        InitializeAOO();
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_SelectFromtblProducts";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable("Products");
        da.Fill(dt);
        GridViewProduct.AutoGenerateSelectButton = true;
        GridViewProduct.AllowPaging = true;
        GridViewProduct.PageSize = 10;
        GridViewProduct.DataSource = dt;
        GridViewProduct.DataBind();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        Fill();
        loadCombobox();
        loadSupplier();
    }

    protected void GridViewProduct_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridViewRow r = GridViewProduct.SelectedRow;
        txtProductID.Text = r.Cells[1].Text;
        txtProductName.Text = r.Cells[2].Text;
        ddlSupplierID.SelectedValue = r.Cells[3].Text;
        ddlCategoryID.SelectedValue = r.Cells[4].Text;
        Double price = Double.Parse(r.Cells[5].Text);
        txtUnitPrice.Text = String.Format("{0:0.##}", price);
        CheckBox cb = (CheckBox)r.Cells[6].Controls[0];
        cbDiscontinued.Checked = cb.Checked;
    }

    void clear()
    {
        txtProductID.Text = "";
        txtProductName.Text = "";
        txtSearch.Text = "";
        txtUnitPrice.Text = "";
        ddlCategoryID.Items.Clear();
        ddlSupplierID.Items.Clear();
        cbDiscontinued.Checked = false;
    }

    void Insert()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_InsertToTblProducts";

            SqlParameter para = new SqlParameter("@productname", SqlDbType.NVarChar);
            para.Size = 40;
            para.Value = txtProductName.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@supplierid", SqlDbType.Int);
            para.Value = ddlSupplierID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@categoryid", SqlDbType.Int);
            para.Value = ddlCategoryID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@unitprice", SqlDbType.Money);
            para.Value = txtUnitPrice.Text;
            cmd.Parameters.Add(para);

            //para = new SqlParameter("@discontinued", SqlDbType.Bit);
            //cmd.Parameters.Add("@discontinued", SqlDbType.Bit).Value = (cbDiscontinued.Checked ? 1 : 0);
            bool discont=true;
            if (cbDiscontinued.Checked)
            {
                discont = true;
            } 
            else discont = false;
            para = new SqlParameter("@discontinued", SqlDbType.Bit);
            para.Value = discont;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            string script = "alert(\"Insert successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }

    void Update()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_UpdateTblProducts";

            SqlParameter para = new SqlParameter(@"productid",SqlDbType.Int);
            para.Value = txtProductID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@productname", SqlDbType.NVarChar);
            para.Size = 40;
            para.Value = txtProductName.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@supplierid", SqlDbType.Int);
            para.Value = ddlSupplierID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@categoryid", SqlDbType.Int);
            para.Value = ddlCategoryID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@unitprice", SqlDbType.Money);
            para.Value = txtUnitPrice.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@discontinued", SqlDbType.Bit);
            cmd.Parameters.Add("@discontinued", SqlDbType.Bit).Value = (cbDiscontinued.Checked ? 1 : 0);

            cmd.ExecuteNonQuery();
            

            string script = "alert(\"Update successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }


    void Delete()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_DeleteProducts";
            SqlParameter para = new SqlParameter(@"productid",SqlDbType.Int);
            para.Value = txtProductID.Text;
            cmd.Parameters.Add(para);
            cmd.ExecuteNonQuery();

            string script = "alert(\"Delete successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }

    void Search()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_SearchProductByNames";
            SqlParameter para = new SqlParameter("@productname", SqlDbType.NVarChar);
            para.Size = 40;
            para.Value = txtSearch.Text.Trim();
            cmd.Parameters.Add(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("Products");
            da.Fill(dt);
            GridViewProduct.DataSource = dt;
            GridViewProduct.DataBind();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }


    protected void GridViewProduct_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        //LoadProduct();
        GridViewProduct.PageIndex = e.NewPageIndex;
        GridViewProduct.DataBind();
    }
    
    protected void btnClearProduct_Click(object sender, EventArgs e)
    {
        clear();
        loadSupplier();
        loadCombobox();
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        Update();
        Fill();
    }
    protected void btnLoadProduct_Click(object sender, EventArgs e)
    {
        Fill();
        clear();
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        Delete();
        Fill();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        Search();
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        Insert();
        Fill();
    }
}
